package project.web;

import java.sql.*;
import java.util.*;
import java.util.Date;

import javax.servlet.http.HttpSession;

public class Accessdb {
	private String dburl = "";
	private String dbuser = "";
	private String dbpass = "";
	
	public void setDbUrl(String url) {
		dburl = url;
	}
	
	public String getDbUrl() {
		return dburl;
	}
	
	public void setDbUser(String user) {
		dbuser = user;
	}
	
	public String getDbUser() {
		return dbuser;
	}
	
	public void setDbPass(String pass) {
		dbpass = pass;
	}
	
	public String getDbPass () {
		return dbpass;
	}
	
	/**
	 * Function for opening connection to the database
	 */
	public Connection getConnection() {
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(getDbUrl(),getDbUser(),getDbPass());			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * Function to close open connection to database
	 */
	public void closeConnection(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public boolean isDuplicateUserName(String name) {
		boolean duplicate = false;
		Connection conn = getConnection();
		if(conn != null) {
			ResultSet rs = null;
			PreparedStatement ps = null;
			try {
				String escapedQuery = new String(name);
				escapedQuery.replace("'", "''");
				String sqlQuery = "SELECT staffuname FROM ajax_exercise.staff WHERE staffuname='" + escapedQuery + "'";
				ps = conn.prepareStatement(sqlQuery);
				rs = ps.executeQuery();
				while (rs.next()) {
					duplicate = true;
					break;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return duplicate;
	}
	
	public List<String> getList(String name){
		Connection conn = getConnection();
		List<String> array = new ArrayList<String>();
		if(conn != null) {
			ResultSet rs = null;
			PreparedStatement ps = null;
			try{
				String escapedQuery = new String(name);
				escapedQuery.replace("'", "''");
				String sqlQuery = "select custname from ajax_exercise.custdata where LOWER(custname) like '" + escapedQuery + "%'";
				ps = conn.prepareStatement(sqlQuery);
				rs = ps.executeQuery();
				while(rs.next()){
					array.add(rs.getString("custname"));
				}
				
			}catch( SQLException e){
				e.printStackTrace();
			}
		}
		return array;
	}
	
	public ResultSet getTable(String name){
		Connection conn = getConnection();
		ResultSet rs = null;
		PreparedStatement ps = null;
		if(conn != null) {
			
			try{
				String escapedQuery = new String(name);
				escapedQuery.replace("'", "''");
				String sqlQuery = "select * from ajax_exercise.custdata where LOWER(custname) like '%" + escapedQuery + "%'";
				ps = conn.prepareStatement(sqlQuery);
				rs = ps.executeQuery();
	
			}catch( SQLException e){
				e.printStackTrace();
			}
		}
		return rs;
	}
	
	public boolean insertTuple(String custname, String custcountry, String custsex, 
			String custemail, String custaddr, String custphone, String custsubtype){
		Connection conn = getConnection();
		Statement st = null;
		boolean result = true;
		if(conn != null) {
			try {
				st = conn.createStatement();
				String sqlQuery = "INSERT INTO ajax_exercise.custdata " +
						"(custname, custcountry, custsex, custemail, custaddr, custphone, custsubtype)" +
						" VALUES ('" + custname +
						"', '" + custcountry +
						"', '" + custsex +
						"', '" + custemail +
						"', '" + custaddr + 
						"', '" + custphone + 
						"', '" + custsubtype +
						"');";
				st.executeUpdate(sqlQuery);

				
			} catch(SQLException e) {
				e.printStackTrace();
				result = false;
			}
			finally {
				try {
					st.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
					result = false;
				}
				closeConnection(conn);	
			}
			return result;
		}
		else{
			System.out.println("Connection Failed!");
			return false;
		}
	}

	public String[] authorize(String login_id, String login_pw){
		Connection conn = getConnection();
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sqlQuery = "select loginid, loginpw, name, user_type from ftwi.member where loginid=\"" + login_id + "\"";

		if(conn!=null){
			try{
				ps = conn.prepareStatement(sqlQuery);
				rs = ps.executeQuery();
				while(rs.next()){
					if(rs.getString(2).equals(login_pw)){
						String[] user = {rs.getString(1), rs.getString(3), rs.getString(4)};
						return user;
					}
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public boolean insertStaff(String username, String name, String email, 
			String division, String password, String secret){
		Connection conn = getConnection();
		Statement st = null;
		boolean result = true;
		if(conn != null) {
			try {
				st = conn.createStatement();
				Date createTime = new Date(java.lang.System.currentTimeMillis());
				Calendar cal = Calendar.getInstance();
				cal.setTime(createTime);
				
			    String date = String.format("%04d-%02d-%02d ",
			            cal.get(Calendar.YEAR),
			            (cal.get(Calendar.MONTH) + 1),
			            cal.get(Calendar.DAY_OF_MONTH)

			           
			        );
			    
			    String time = String.format("%02d:%02d:%02d", 
			    		cal.get(Calendar.HOUR_OF_DAY),
			            cal.get(Calendar.MINUTE),
			            cal.get(Calendar.SECOND));
				String sqlQuery = "INSERT INTO ajax_exercise.staff " +
						"(staffuname, staffpasswd, staffname, staffemail, staffdivision, secretcode, regdate)" +
						" VALUES ('" + username +
						"', '" + password +
						"', '" + name +
						"', '" + email +
						"', '" + division + 
						"', '" + secret + 
						"', '" + date + " " + time +
						"');";
				st.executeUpdate(sqlQuery);

				
			} catch(SQLException e) {
				e.printStackTrace();
				result = false;
			}
			finally {
				try {
					st.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
					result = false;
				}
				closeConnection(conn);	
			}
			return result;
		}
		else{
			System.out.println("Connection Failed!");
			return false;
		}
	}
	public ResultSet retProfile(HttpSession session, String ID){
		Connection conn = getConnection();
		ResultSet rs = null;
		PreparedStatement ps = null;
		if(conn != null) {
			try{
				String escapedQuery = ID;
				escapedQuery.replace("'", "''");
				String sqlQuery = "select * from ajax_exercise.staff where staffid=" + escapedQuery;
				ps = conn.prepareStatement(sqlQuery);
				rs = ps.executeQuery();
	
			}catch( SQLException e){
				e.printStackTrace();
			}
		}
		return rs;
	}
}

